package sample.util;

/**
 * Created by lenovo on 2017/1/6.
 */

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.*;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcelData {

    public static  List<Map<String,Object>> readXml(String fileName,int sheetIndex){
        List<Map<String,Object>> data=new ArrayList<>();
        String[] strArr=null;
        List<String> title=new ArrayList<String>();
        boolean isE2007 = false;    //判断是否是excel2007格式
        if(fileName.endsWith("xlsx"))
            isE2007 = true;
        try {
            InputStream input = new FileInputStream(fileName);  //建立输入流
            Workbook wb  = null;
            //根据文件格式(2003或者2007)来初始化
            if(isE2007)
                wb = new XSSFWorkbook(input);
            else
                wb = new HSSFWorkbook(input);
            Sheet sheet = wb.getSheetAt(sheetIndex); //获得第一个表单
            Iterator<Row> rows = sheet.rowIterator(); //获得第一个表单的迭代器
            DecimalFormat df = new DecimalFormat("0");
            while (rows.hasNext()) {
                Row row = rows.next();  //获得行数据
                System.out.println("Row #" + row.getRowNum());  //获得行号从0开始
                Map<String,Object> map=new HashMap<>();
                Iterator<Cell> cells = row.cellIterator();    //获得第一行的迭代器
                while (cells.hasNext()) {
                    Cell cell = cells.next();
                    System.out.println("Cell #" + cell.getColumnIndex());
                    switch (cell.getCellType()) {   //根据cell中的类型来输出数据
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            getCellStringData(cell,strArr,map,df.format(cell.getNumericCellValue()),row);
                            break;
                        case HSSFCell.CELL_TYPE_STRING:
                            if(row.getRowNum()==0){
                                title.add(cell.getStringCellValue());
                            }
                            getCellStringData(cell,strArr,map,String.valueOf(cell.getStringCellValue()),row);
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN:
                            getCellBooleanData(cell,strArr,map,cell.getBooleanCellValue(),row);
                            break;
                        case HSSFCell.CELL_TYPE_FORMULA:
                            try{
                                if(row.getRowNum()==0){
                                    title.add(cell.getStringCellValue());
                                }
                                getCellStringData(cell,strArr,map,df.format(cell.getNumericCellValue()),row);
                            }catch(IllegalStateException e){
                                  try {
                                      getCellStringData(cell,strArr,map,String.valueOf(cell.getStringCellValue()),row);
                                  }catch(IllegalStateException e1){
                                      getCellStringData(cell,strArr,map," ",row);
                                  }
                            }
                            break;
                        default:
                            getCellStringData(cell,strArr,map," ",row);
                            break;
                    }
                }
                if(row.getRowNum()==0){
                    strArr=new String[title.size()];
                    for(int i=0;i<title.size();i++){
                        strArr[i]=title.get(i);
                    }
                }
                data.add(map);
            }
        } catch (IOException ex) {
            ex.printStackTrace();
        }
        return data;
    }
    public static void  getCellStringData( Cell cell,String[] strArr,Map<String,Object> map,String str,Row row){
        if(row.getRowNum()>0){
            for(int i=0;i<strArr.length;i++){
                if(cell.getColumnIndex()==i) {
                    map.put(strArr[i], str);
                    break;
                }
            }
        }
    }
    public static void  getCellDoubleData( Cell cell,String[] strArr,Map<String,Object> map,Double str,Row row){
        if(row.getRowNum()>0){
            for(int i=0;i<strArr.length;i++){
                if(cell.getColumnIndex()==i) {
                    map.put(strArr[i], str);
                    break;
                }
            }
        }
    }
    public static void  getCellBooleanData( Cell cell,String[] strArr,Map<String,Object> map,Boolean str,Row row){
        if(row.getRowNum()>0){
            for(int i=0;i<strArr.length;i++){
                if(cell.getColumnIndex()==i) {
                    map.put(strArr[i], str);
                    break;
                }
            }
        }
    }

}
